package com.yu.dv.strategy.metadata.impl;

import com.yu.dv.entity.TableInfo;
import com.yu.dv.strategy.metadata.MetadataTemplate;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class H2MetadataStrategy extends MetadataTemplate {

    @Override
    protected String getCatalog(Connection connection) throws SQLException {
        // H2中catalog通常是数据库名称
        try {
            String catalog = connection.getCatalog();
            log.debug("H2 catalog: {}", catalog);
            return catalog;
        } catch (Exception e) {
            log.debug("获取H2 catalog失败: {}", e.getMessage());
            return null;
        }
    }

    @Override
    protected String getSchema(Connection connection) throws SQLException {
        // H2 默认schema，尝试多种方式获取
        try {
            String schema = connection.getSchema();
            if (schema != null) {
                log.debug("H2 schema from connection: {}", schema);
                return schema;
            }
        } catch (Exception e) {
            log.debug("从连接获取schema失败: {}", e.getMessage());
        }

        // 尝试从元数据获取
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            try (ResultSet rs = metaData.getSchemas()) {
                while (rs.next()) {
                    String schemaName = rs.getString("TABLE_SCHEM");
                    if ("PUBLIC".equalsIgnoreCase(schemaName)) {
                        log.debug("H2使用PUBLIC schema");
                        return "PUBLIC";
                    }
                }
            }
        } catch (Exception e) {
            log.debug("从元数据获取schema失败: {}", e.getMessage());
        }

        log.debug("H2使用默认schema: PUBLIC");
        return "PUBLIC";
    }

    @Override
    protected String[] getTableTypes() {
        // H2支持的表类型
        return new String[]{"TABLE", "VIEW"};
    }

    @Override
    protected String[][] getParameterCombinations(String catalog, String schema) {
        // H2特定的参数组合尝试
        return new String[][]{
                {catalog, "PUBLIC"},      // 明确指定PUBLIC schema
                {catalog, "public"},      // 小写public
                {catalog, null},          // 不指定schema
                {null, "PUBLIC"},         // 不指定catalog，指定schema
                {null, null},             // 无限制
                {"%", "%"}               // 通配符
        };
    }

    @Override
    protected List<TableInfo> getTablesWithFallback(Connection connection, String catalog, String schema) throws Exception {
        List<TableInfo> tables = new ArrayList<>();

        log.info("使用H2备用方案获取表信息，catalog: {}, schema: {}", catalog, schema);

        // 方法1: 使用INFORMATION_SCHEMA查询
        String query = "SELECT " +
                "TABLE_NAME, " +
                "CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'TABLE' ELSE TABLE_TYPE END as TABLE_TYPE, " +
                "REMARKS " +
                "FROM INFORMATION_SCHEMA.TABLES " +
                "WHERE TABLE_SCHEMA = COALESCE(?, 'PUBLIC') " +
                "AND TABLE_TYPE IN ('BASE TABLE', 'VIEW') " +
                "ORDER BY TABLE_NAME";

        try (PreparedStatement stmt = connection.prepareStatement(query)) {
            stmt.setString(1, schema);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    TableInfo table = createTableInfoFromResultSet(rs);
                    if (table != null) {
                        tables.add(table);
                    }
                }
            }
        } catch (SQLException e) {
            log.warn("INFORMATION_SCHEMA查询失败: {}", e.getMessage());
            // 继续尝试其他方法
        }

        // 方法2: 如果上述方法失败，尝试直接查询系统表
        if (tables.isEmpty()) {
            tables = getTablesFromSystemTables(connection, schema);
        }

        log.info("H2备用方案获取到 {} 张表", tables.size());
        return tables;
    }

    /**
     * 从系统表获取表信息
     */
    private List<TableInfo> getTablesFromSystemTables(Connection connection, String schema) {
        List<TableInfo> tables = new ArrayList<>();

        try {
            // 查询H2的系统表
            String query = "SELECT " +
                    "TABLE_NAME, " +
                    "CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'TABLE' ELSE TABLE_TYPE END as TABLE_TYPE, " +
                    "'' as REMARKS " +
                    "FROM INFORMATION_SCHEMA.TABLES " +
                    "WHERE TABLE_CATALOG = ? " +
                    "AND TABLE_SCHEMA = COALESCE(?, 'PUBLIC')";

            try (PreparedStatement stmt = connection.prepareStatement(query)) {
                stmt.setString(1, connection.getCatalog());
                stmt.setString(2, schema);

                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        String tableName = rs.getString("TABLE_NAME");
                        String tableType = rs.getString("TABLE_TYPE");
                        String remarks = rs.getString("REMARKS");

                        TableInfo table = new TableInfo(tableName, tableType, remarks);
                        tables.add(table);
                    }
                }
            }
        } catch (Exception e) {
            log.warn("从系统表获取表信息失败: {}", e.getMessage());
        }

        return tables;
    }

    @Override
    protected void enrichTablesInfo(DatabaseMetaData metaData, String catalog,
                                    String schema, List<TableInfo> tables) throws SQLException {
        // H2需要特殊处理schema参数
        String effectiveSchema = (schema == null) ? "PUBLIC" : schema;

        for (TableInfo table : tables) {
            try {
                // 明确指定schema
                table.setColumns(getColumnsSafe(metaData, catalog, effectiveSchema, table.getName()));
                table.setPrimaryKeys(getPrimaryKeysSafe(metaData, catalog, effectiveSchema, table.getName()));
                table.setIndexes(getIndexesSafe(metaData, catalog, effectiveSchema, table.getName()));
            } catch (SQLException e) {
                log.warn("获取表 {} 的详细信息失败: {}", table.getName(), e.getMessage());
                // 尝试不指定schema
                try {
                    table.setColumns(getColumnsSafe(metaData, catalog, null, table.getName()));
                    table.setPrimaryKeys(getPrimaryKeysSafe(metaData, catalog, null, table.getName()));
                    table.setIndexes(getIndexesSafe(metaData, catalog, null, table.getName()));
                } catch (SQLException ex) {
                    log.warn("第二次尝试获取表 {} 的详细信息也失败: {}", table.getName(), ex.getMessage());
                }
            }
        }
    }

    /**
     * 重写表信息创建方法，处理H2的特殊情况
     */
    @Override
    protected TableInfo createTableInfoFromResultSet(ResultSet rs) throws SQLException {
        String tableName = rs.getString("TABLE_NAME");
        if (tableName == null) return null;

        String tableType = rs.getString("TABLE_TYPE");
        String remarks = rs.getString("REMARKS");

        // H2的表类型可能是"BASE TABLE"，需要转换为"TABLE"
        if ("BASE TABLE".equalsIgnoreCase(tableType)) {
            tableType = "TABLE";
        }

        return new TableInfo(tableName, tableType, remarks);
    }
}